﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    /// <summary>
    /// 保修卡，调查表
    /// </summary>
    public  class UWarrantyCardDAL
    {
        #region 保修卡部份
       /// <summary>
       /// 添加何修卡
       /// </summary>
       /// <param name="obj"></param>
       /// <returns></returns>
        public static int Add(UWarrantyCardInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_Add", obj.MachineType.ToUpper().Trim(), obj.SerialNumber.ToUpper().Trim(), obj.EngineNumber, obj.IntoDate, obj.IntoHours, obj.IntoAddress, obj.IntoSpecial, obj.IntoSpecialOther, obj.IntoConditi, obj.IntoConditiOther, obj.CustomerName, obj.CustomerTel, obj.CustomerMobile, obj.CustomerIDCard, obj.CustomerAddress, obj.AgentName, obj.AgentTel, obj.AgentSvrPer, obj.AgentSvrPerTel, obj.AgentAddress, obj.IsVip, obj.Birthday, obj.IsHandCard, obj.IsKeepSvr, obj.Remark, obj.IsBreaker, obj.ScanImg1, obj.ScanImg2,obj.ScanImgJiHao,obj.ScanImgWorkTime,obj.ScanImgHeYing,obj.ScanImgJiaJi, obj.ScanImgPuoChui, obj.ScanImgYuanBao1, obj.ScanImgYuanBao2, obj.zipaoqixian, obj.LYXTFHGZS1, obj.LYXTFHGZS2);
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
        }
        public static void AddUQuestionnaire(List<UQuestionnaireInfo> obj)
        {
            foreach (UQuestionnaireInfo item in obj)
            {
                UWarrantyCardDAL.UQuestionnaire_Add(item);
            }
        }
        public static bool Delete(int UWCID)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_Delete", UWCID);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 根据ID获取保修卡信息
        /// </summary>
        /// <param name="UWCID"></param>
        /// <returns></returns>
        public static IDataReader GetByUWCID(int UWCID)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();				
            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_GetByUWCID", UWCID);
            return DB.ExecuteReader(dbCommand);
	    }

       // public static DataTableCollection GetPagination(string MachineType,string SerialNumber, string CustomerName, string CustomerIDCard, string AgentName, string Birthday, string AgentAddress,int IsVip,string IntoDateFrom,string IntoDateTo,  int PageIndex, int PageSize, string Sort, ref int TotalCount)

        public static DataTableCollection GetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();
            //if (!string.IsNullOrEmpty(AgentName))
            //{
            //    AgentName = "'" + AgentName + "'";
            //    AgentName = AgentName.Replace(",", "','");
            //}
            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_GetPagination");
            //DB.AddInParameter(dbCommand, "MachineType", DbType.String, MachineType);
            //DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, SerialNumber.ToUpper().Trim());
            //DB.AddInParameter(dbCommand, "CustomerName", DbType.String,CustomerName);
            //DB.AddInParameter(dbCommand, "CustomerIDCard", DbType.String,CustomerIDCard);
            //DB.AddInParameter(dbCommand, "AgentName", DbType.String,AgentName);
            //DB.AddInParameter(dbCommand, "Birthday", DbType.String,Birthday);
            //DB.AddInParameter(dbCommand, "AgentAddress", DbType.String,AgentAddress);
            //DB.AddInParameter(dbCommand, "IsVip", DbType.Int32, IsVip);
            //DB.AddInParameter(dbCommand, "IntoDateFrom", DbType.String, IntoDateFrom);
            //DB.AddInParameter(dbCommand, "IntoDateTo", DbType.String, IntoDateTo);
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32,PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32,PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String,Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32,8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;
				
            TotalCount=Convert.ToInt32( DB.GetParameterValue(dbCommand,"TotalCount"));
            return dt;
	    }
        /// <summary>
        /// 修改保修卡
        /// </summary>
        /// <returns></returns>
        public static bool Update(UWarrantyCardInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_Update", obj.UWCID, obj.MachineType.ToUpper().Trim(), obj.SerialNumber.ToUpper().Trim(), obj.EngineNumber, obj.IntoDate, obj.IntoHours, obj.IntoAddress, obj.IntoSpecial, obj.IntoSpecialOther, obj.IntoConditi, obj.IntoConditiOther, obj.CustomerName, obj.CustomerTel, obj.CustomerMobile, obj.CustomerIDCard, obj.CustomerAddress, obj.AgentName, obj.AgentTel, obj.AgentSvrPer, obj.AgentSvrPerTel, obj.AgentAddress, obj.IsVip, obj.Birthday, obj.IsHandCard, obj.IsKeepSvr, obj.Remark, obj.IsBreaker, obj.ScanImg1, obj.ScanImg2, obj.ScanImgJiHao, obj.ScanImgWorkTime, obj.ScanImgHeYing, obj.ScanImgJiaJi, obj.ScanImgPuoChui, obj.ScanImgYuanBao1, obj.ScanImgYuanBao2,obj.zipaoqixian, obj.LYXTFHGZS1, obj.LYXTFHGZS2);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static IDataReader GetBySerialNumber(string SerialNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_GetBySerialNumber", SerialNumber.ToUpper().Trim());
            return DB.ExecuteReader(dbCommand);
        }

        public static DataTable GetAgentMacNum()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_GetAgentMacNum");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        #endregion

        #region 调查表
        /// <summary>
        /// 添加调查表项
        /// </summary>
        /// <param name="obj"></param>
        private static void UQuestionnaire_Add(UQuestionnaireInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UQuestionnaire_Add", obj.UWCID, obj.SerialNumber.ToUpper().Trim(), obj.UQTID, obj.InputValue);
            DB.ExecuteScalar(dbCommand);
        }
        /// <summary>
        /// 获取调查表所有项
        /// </summary>
        /// <param name="UWCID"></param>
        /// <returns></returns>
        public static IDataReader GetUQuestionnaireByUWCID(int UWCID)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("UQuestionnaire_GetByUWCID", UWCID);
            return DB.ExecuteReader(dbCommand);
	    }
        
        #endregion

        #region 调查表模板
        /// <summary>
        /// 根据上级ID获取所有的调查表模板数据
        /// </summary>
        /// <param name="ParId"></param>
        /// <returns></returns>
        public static IDataReader GetAllUQuestionTempleteByParId(int ParId)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("UQuestionTemplete_GetAllByParId", ParId);
            return DB.ExecuteReader(dbCommand);
	    }
        #endregion

        public static DataTable GetReport(int ParId,string fromdate,string todate)
        { 
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UQuestionnaire_GetReport", ParId, fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        public static int GetReprotSUMCount(string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UQuestionnaire_GetReprotSUMCount", fromdate, todate);
            return Convert.ToInt32( DB.ExecuteScalar(dbCommand));
        }

        #region 报表
        /// <summary>
        /// 根据时间段统计各月交机第一次出现故障的平均值
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection GetFirstBugTimeReport(string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UWarrantyCard_GetFirstBugTimeReport", fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        #endregion
    }
}
